<?php
/**
 * @author 
 * @copyright 2014
 */
 
 
class KlgbModel extends CI_Model
{
    var $_menus = array();
    
	function __construct(){
		parent::__construct('klgb');
	}
	function getList($search){
		$sql = "
				SELECT kl.*, u.fullname as nguoiketluan, u2.fullname as chutri, lgb.names as loaigiaoban, lkl.`names` as loaiketluan, u3.fullname as chuyenquan
				FROM klgb kl
				LEFT JOIN users u on u.id = kl.nguoiketluan
				LEFT JOIN users u2 on u2.id = kl.chutri
				LEFT JOIN users u3 on u3.id = kl.nvchuyenquan
				LEFT JOIN loaigiaoban lgb on lgb.id = kl.loaigiaoban
				LEFT JOIN hinhthuckls lkl on lkl.id = kl.loaikl
				WHERE kl.isdelete = 0
				";
		if($search['ngaygiaoban'] != ""){
			$sql.= " AND kl.ngaygiaoban = '".date('Y-m-d',strtotime($search['ngaygiaoban']))."' ";
		}
		if($search['sovanban'] != ""){
			$sql.= " AND kl.sovanban like '%".$search['sovanban']."%' ";
		}
		if($search['loaikl'] != ""){
			$sql.= " AND kl.loaikl in (".$search['loaikl'].") ";
		}
		if($search['chutri'] != ""){
			$sql.= " AND kl.chutri in (".$search['chutri'].") ";
		}
		if($search['nvchuyenquan'] != ""){
			$sql.= " AND kl.nvchuyenquan in (".$search['nvchuyenquan'].") ";
		}
		return $sql;
	}
	function getTotal($search){
		$sql = "
				SELECT count(1) total
				FROM klgb kl
				LEFT JOIN users u on u.id = kl.nguoiketluan
				LEFT JOIN users u2 on u2.id = kl.chutri
				LEFT JOIN users u3 on u3.id = kl.nvchuyenquan
				LEFT JOIN loaigiaoban lgb on lgb.id = kl.loaigiaoban
				LEFT JOIN hinhthuckls lkl on lkl.id = kl.loaikl
				WHERE kl.isdelete = 0
				";
		if($search['ngaygiaoban'] != ""){
			$sql.= " AND kl.ngaygiaoban = '".date('Y-m-d',strtotime($search['ngaygiaoban']))."' ";
		}
		if($search['sovanban'] != ""){
			$sql.= " AND kl.sovanban like '%".$search['sovanban']."%' ";
		}
		if($search['loaikl'] != ""){
			$sql.= " AND kl.loaikl in (".$search['loaikl'].") ";
		}
		if($search['chutri'] != ""){
			$sql.= " AND kl.chutri in (".$search['chutri'].") ";
		}
		if($search['nvchuyenquan'] != ""){
			$sql.= " AND kl.nvchuyenquan in (".$search['nvchuyenquan'].") ";
		}
		$query = $this->model->query($sql)->execute();
		if(!empty($query->total)){
			return $query->total;
		}
		else{
			return 0;
		}
	}
	function loaiketluan(){
		return $this->model->table('hinhthuckls')->where('isdelete',0)->find_combo('id','names');
	}
	function loaigiaoban(){
		return $this->model->table('loaigiaoban')->where('isdelete',0)->find_combo('id','names');
	}
	function listChutri(){
		$sql = "
				select u.id, u.fullname
				from users u
				left join groups g on u.groups = g.id
				where u.isdelete = 0
				and g.types = 'GD';
		";
		$query = $this->model->query($sql)->execute();
		$arr = array();
		foreach($query as $item){
			$arr[$item->id] = $item->fullname;
		}
		return $arr;
	}
	function listPhongban(){
		return $this->model->table('department')->where('isdelete',0)->order_by('orders','ASC')->find_combo('id','names');
	}
	function listChuyenquan(){
		$sql = "
				select u.id, u.fullname
				from users u
				left join groups g on u.groups = g.id
				where u.isdelete = 0
				and g.types = 'CQ';
		";
		$query = $this->model->query($sql)->execute();
		$arr = array();
		foreach($query as $item){
			$arr[$item->id] = $item->fullname;
		}
		return $arr;
	}
	
	function saves($data,$phongban,$id){
		$this->db->trans_start();
		$login = $this->pub->GetSession('logins');
		if(empty($id)){
			$data['ngaytao'] =  gmdate("Y-m-d H:s:i", time() + 7 * 3600);
			$data['nguoitao'] = $login['username']; 	
			$id = $this->model->save('',$data);
			$this->saveDept($id,$phongban);
		}
		else{
			$data['ngaysua'] =  gmdate("Y-m-d H:s:i", time() + 7 * 3600);
			$data['nguoisua'] = $login['username']; 	
			$this->model->where('id',$id)->update($data);
			$this->saveDept($id,$phongban);
		}
		$this->db->trans_complete();
		if ($this->db->trans_status() === FALSE){
			return 0;
		}
		else{
			return 1;	
		}
	}
	
	function saveDept($id,$phongban){
		if($id != "" && $phongban != ""){
			$login = $this->pub->GetSession('logins');
			$this->model->table('klphongban')
						->where('idkl',$id)
						->where('types',0)
						->delete();
			$arr = explode(",",$phongban);
			foreach($arr as $key=>$idphongban){	
				$array = array();
				$array['idkl'] = $id;
				$array['idphongban'] = $idphongban;
				$array['types'] = 0;
				$array['usercreate'] = $login['username']; 	
				$array['datecreate'] =  gmdate("Y-m-d H:s:i", time() + 7 * 3600);
				$this->model->table('klphongban')->insert($array);
			}
		}
	}
	function getIDPhongban($id){
		$query = $this->model->table('klphongban')
						->where('idkl',$id)
						->find_combo('idphongban','noidung');
		return $query;
	}
	function deletes($str,$item){
		if(!empty($item)){
			$this->model->where('id',$item)->update(array('isdelete'=>1));
			echo 1;
		}
		else{
			$array = explode(',',$str);
			foreach($array as $k=>$id){
				$this->model->where('id',$id)->update(array('isdelete'=>1));	
				echo 1;
			}
		}
	}
}